﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Com.LongER.Framework.Utils;
using Com.LongER.Business.ZHM009.ZHM009DSTableAdapters;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
using Com.LongER.Framework.Utils.LogUtils;

namespace Com.LongER.Business.ZHM009
{
    public class CZHM009 : ABussinessBase
    {
        private Log log = Log.GetInstance();

        /// <summary>
        /// 获取下拉框的值
        /// </summary>
        /// <returns></returns>
        public ZHM009DS.M_TONGYONGDataTable GetLists()
        {
            String connectionString = ConnectionStringUtil.GetConnectionString();
            M_TONGYONGTableAdapter ta = new M_TONGYONGTableAdapter(connectionString);

            ZHM009DS.M_TONGYONGDataTable tb = ta.GetData();
            ta.Connection.Close();
            return tb;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="htParam"></param>
        /// <returns></returns>
        public ZHM009DS.M_KEHUDataTable GetKehuList(Dictionary<string, string> dicParam)
        {
            string strKEHU_CD = dicParam["KEHU_CD"];
            string strSHO = dicParam["SHO"];
            string strHEZUO_XINGZHI = dicParam["HEZUO_XINGZHI"];

            string strBETWEEN_START = ((int.Parse(dicParam["PAGE_NO"]) - 1) * 5 + 1).ToString();
            string strBETWEEN_END = (int.Parse(dicParam["PAGE_NO"]) * 5).ToString();

            ////DB接続文字列取得
            var connectionString = ConnectionStringUtil.GetConnectionString();
            SqlConnection con = new SqlConnection(connectionString);
            con.Open();
            string cnnstr = string.Empty;

            #region sqlContent
            // C#コード自動作成
            StringBuilder objSqlContent = new StringBuilder();
            objSqlContent.Append(" SELECT                                                                          ");
            objSqlContent.Append("     *                                                                           ");
            objSqlContent.Append(" FROM                                                                            ");
            objSqlContent.Append("     (                                                                           ");
            objSqlContent.Append("         SELECT                                                                  ");
            objSqlContent.Append("             K.KEHU_CD                                                            ");
            objSqlContent.Append("             ,K.KEHU_HANZI                                                     ");
            objSqlContent.Append("             ,KK.CODE1_HANZI SHO                                                 ");
            objSqlContent.Append("             ,H.CODE1_HANZI HEZUO_XINGZHI                                                 ");
            objSqlContent.Append("             ,K.LIANXI_REN                                                           ");
            objSqlContent.Append("             ,K.TEL_NUM1                                                           ");
            objSqlContent.Append("             ,K.TEL_NUM2                                                           ");
            objSqlContent.Append("             ,K.FAX1                                                           ");
            objSqlContent.Append("             ,K.MAIL_ADDR                                                        ");
            objSqlContent.Append("             ,rn = ROW_NUMBER                                                    ");
            objSqlContent.Append("             (                                                                   ");
            objSqlContent.Append("             )                                                                   ");
            objSqlContent.Append("             OVER                                                                ");
            objSqlContent.Append("             (                                                                   ");
            objSqlContent.Append("             ORDER BY                                                            ");
            objSqlContent.Append("                 K.KEHU_CD ASC                                                   ");
            objSqlContent.Append("             )                                                                   ");
            objSqlContent.Append("         FROM                                                                    ");
            objSqlContent.Append("             M_KEHU K                                                            ");
            objSqlContent.Append("             LEFT JOIN                                                           ");
            objSqlContent.Append("             M_TONGYONG                                                          ");
            objSqlContent.Append("             KK                                                                  ");
            objSqlContent.Append("             ON                                                                  ");
            objSqlContent.Append("                 K.SHO = KK.KEY1                                            ");
            objSqlContent.Append("             AND KK.CODE_CD = 'SSQF'                                             ");
            objSqlContent.Append("             LEFT JOIN                                                           ");
            objSqlContent.Append("             M_TONGYONG                                                          ");
            objSqlContent.Append("             H                                                                   ");
            objSqlContent.Append("             ON                                                                  ");
            objSqlContent.Append("                 K.HEZUO_XINGZHI = H.KEY1                                        ");
            objSqlContent.Append("             AND H.CODE_CD = 'HZXZ'                                              ");
            objSqlContent.Append("         WHERE                                                                   ");
            objSqlContent.Append("                 1 = 1                                                           ");
            if (!String.IsNullOrWhiteSpace(strKEHU_CD))
                objSqlContent.Append("             AND K.KEHU_CD = @KEHU_CD                                        ");
            if (!String.IsNullOrWhiteSpace(strSHO))
                objSqlContent.Append("             AND K.SHO = @SHO                                                ");
            if (!String.IsNullOrWhiteSpace(strHEZUO_XINGZHI))
                objSqlContent.Append("             AND K.HEZUO_XINGZHI = @HEZUO_XINGZHI                            ");
            objSqlContent.Append("     )                                                                           ");
            objSqlContent.Append("     A                                                                           ");
            objSqlContent.Append(" WHERE                                                                           ");
            objSqlContent.Append("         A.rn BETWEEN @BETWEEN_START AND @BETWEEN_END;                           ");
            #endregion

            cnnstr = objSqlContent.ToString();

            log.Debug("CZHM009:" + cnnstr);

            SqlCommand cmd = new SqlCommand(cnnstr, con);
            cmd.CommandText = cnnstr;
            cmd.CommandType = System.Data.CommandType.Text;
            //客户CD
            if (!String.IsNullOrWhiteSpace(strKEHU_CD))
                cmd.Parameters.Add(getSqlParameter("KEHU_CD", SqlDbType.Char, strKEHU_CD));
            //所属区分
            if (!String.IsNullOrWhiteSpace(strSHO))
                cmd.Parameters.Add(getSqlParameter("SHO", SqlDbType.Char, strSHO));
            //合作性质
            if (!String.IsNullOrWhiteSpace(strHEZUO_XINGZHI))
                cmd.Parameters.Add(getSqlParameter("HEZUO_XINGZHI", SqlDbType.Char, strHEZUO_XINGZHI));
            //
            if (!String.IsNullOrWhiteSpace(strBETWEEN_START))
                cmd.Parameters.Add(getSqlParameter("BETWEEN_START", SqlDbType.Int, int.Parse(strBETWEEN_START)));
            //
            if (!String.IsNullOrWhiteSpace(strBETWEEN_END))
                cmd.Parameters.Add(getSqlParameter("BETWEEN_END", SqlDbType.Int, int.Parse(strBETWEEN_END)));

            SqlDataReader odr = cmd.ExecuteReader();

            ZHM009DS.M_KEHUDataTable tb = new ZHM009DS.M_KEHUDataTable();
            if (odr.HasRows)
            {
                // データ有り
                while (odr.Read())
                {
                    ZHM009DS.M_KEHURow dr = tb.NewM_KEHURow();
                    //row 1
                    dr.KEHU_CD = odr["KEHU_CD"].ToString();
                    dr.KEHU_HANZI = odr["KEHU_HANZI"].ToString();
                    dr.SHO = odr["SHO"].ToString();
                    dr.HEZUO_XINGZHI = odr["HEZUO_XINGZHI"].ToString();
                    dr.LIANXI_REN = odr["LIANXI_REN"].ToString();
                    dr.TEL_NUM1 = odr["TEL_NUM1"].ToString();
                    dr.TEL_NUM2 = odr["TEL_NUM2"].ToString();
                    dr.FAX1 = odr["FAX1"].ToString();
                    dr.MAIL_ADDR = odr["MAIL_ADDR"].ToString();
                    tb.AddM_KEHURow(dr);
                }
            }

            con.Close();
            return tb;
        }


    }
}